/**

This file is part of MaCI/GIMnet.

MaCI/GIMnet is free software: you can redistribute it and/or modify it 
under the terms of the GNU Lesser General Public License as published 
by the Free Software Foundation, either version 3 of the License, or 
(at your option) any later version.

MaCI/GIMnet is distributed in the hope that it will be useful, but WITHOUT 
ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or 
FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public 
License for more details.

You should have received a copy of the GNU Lesser General Public 
License along with GIMnet. (See COPYING.LESSER) If not, see 
<http://www.gnu.org/licenses/>.

**/
/**
 * \file
 * \brief Handling Motti Map objects in SQL DB.
 * \author Antti Maula <antti.maula@tkk.fi>
 */
#ifndef _MACI_INTERFACE_SQL_EXTENSION_OBJECTDB_HPP_
#define _MACI_INTERFACE_SQL_EXTENSION_OBJECTDB_HPP_

#include "SQLQuery.hpp"
#include "SQLResult.hpp"
#include "SQLClient.hpp"
#include "SQLResultTypes.hpp"
#include "SQLExtension.hpp"
#include <string>
#include <vector>
#include <sstream>
#include <iostream>

namespace MaCI {
  namespace SQL {
    namespace Ext {

      struct SObjectNameAndId
      {
      public:
        SObjectNameAndId(void) 
          : id(),
            name()
        {}
        
        
        /** Constructor accepting single TSQLRow.
         */
        SObjectNameAndId(const TSQLRow &aRow)
          : id(),
            name()
        {
          DecodeSQLResultRow(aRow);
        }


        /** Construct by data.
         *
         */
        SObjectNameAndId(const std::string &aName)
          : id(),
            name(aName)
        {}
        

        /** Destructor.
         */
        ~SObjectNameAndId() 
        {}



        /** This function attempts to decode a SQL Row as type
         * SObject.
         *
         * This function takes a TSQLRow as parameter, and attempts to
         * decode all fields to current container. This function will
         * fail if 1) the SQLRow size doesn't match required size of
         * 11 fields.  2) if any of the integer type fields doesn't
         * correctly convert to integer.
         *
         * \note This Decoder expects to get ALL available fields. If
         * the query is made so, that result set DOESN'T contain all
         * the available fields (by selecting fields), a special
         * processing function needs to be written. (Or contents be
         * taken care of manually)
         *
         * @param[in] aRow      TSQLRow container to decode as 
         *                      SObject.
         * @return              'true' if all conditions mentioned 
         *                      above are met, otherwise 'false'.
         */
        bool DecodeSQLResultRow(const TSQLRow &aRow) {
          bool result = false;

          // This is not a magic value, this is the number of fields
          // in row. Very static. Rows without 'result' check are fields
          // that can be NULL. (Not very good handling though)
          if (aRow.size() == 2) {
            result = aRow.at(0).ConvertTo(id);
            result &= aRow.at(1).ConvertTo(name);
          }
          return result;
        }

      public:
        unsigned int id;         ///< SQL Identifier of Row
        std::string name;        ///< Name of entity
      };


      /** Single row of ObjectDB type.
       */
      struct SObject
      {
      public:
        /** Empty constructor.
         */
        SObject(void) 
          : id()
        {}
        
        
        /** Constructor accepting single TSQLRow.
         */
        SObject(const TSQLRow &aRow)
          : id()
        {
          DecodeSQLResultRow(aRow);
        }


        /** Construct by data.
         *
         *
        SObject(const unsigned int aGroupID, 
                           const unsigned int aLogicalID, 
                           const unsigned int aTypeID, 
                           const float aX, const float aY)
          : id(), 
            group_id(aGroupID),
            logical_id(aLogicalID), 
            type_id(aTypeID), 
            x(aX), 
            y(aY) 
        {}
        */

        /** Destructor.
         */
        ~SObject() 
        {
        }

        
        /** This function attempts to decode a SQL Row as type
         * SObject.
         *
         * This function takes a TSQLRow as parameter, and attempts to
         * decode all fields to current container. This function will
         * fail if 1) the SQLRow size doesn't match required size of
         * 11 fields.  2) if any of the integer type fields doesn't
         * correctly convert to integer.
         *
         * \note This Decoder expects to get ALL available fields. If
         * the query is made so, that result set DOESN'T contain all
         * the available fields (by selecting fields), a special
         * processing function needs to be written. (Or contents be
         * taken care of manually)
         *
         * @param[in] aRow      TSQLRow container to decode as 
         *                      SObject.
         * @return              'true' if all conditions mentioned 
         *                      above are met, otherwise 'false'.
         */
        bool DecodeSQLResultRow(const TSQLRow &aRow) {
          return false;
        }
        
      public:
        unsigned int id;

      };


      /** Array of SObject elements.
       */
      typedef std::vector<SObject> TObjectResultTable;


      /** Array of SObjectNameAndId elements.
       */
      typedef std::vector<SObjectNameAndId> TObjectNameAndIdResultTable;


      /** Helper class for handling ObjectDB storage format.
       * 
       * This is a helper class for handling 'MaCI Service' storage
       * format over the SQL interface. This storage format is not
       * directly bound to any existing interface, and hence is a good
       * example/starting point for a 'SQL Extension module'
       *
       * \note This helper class is 'Header Only' implementation.
       *
       * \see MaCI::SQL::Ext namespace for more details on helper
       * classes.
       */
      class CObjectDB : public CSQLExtension
      {
      public:
        /** Constructor.
         *
         * @param[in] aSQLClient Pointer to initialized and connect
         * SQLClient instance. This instance is used for communicating
         * with the SQL interface.
         * @param[in] aTableName Table name to query for.
         *
         */
        CObjectDB(CSQLClient *aSQLClient, const std::string &aTargetTable)
          : CSQLExtension(aSQLClient, aTargetTable) { 
        }

        /** Destructor.
         */
        ~CObjectDB(void) {
        }
       

        /** Create table with Object DB schema.
         *
         * This function attempts to create a new table with
         * Object layout. If the table exists or cannot be
         * created, this function returns NULL. This function uses the
         * internally set TargetTable, so be sure to set the target
         * table before attempting to call this.
         *
         * @param[in] aTimeout_ms Maximum time to wait for the operation to complete.
         * @return              'true' on success, 'false' on error.
         *
         */
        bool CreateTable(const unsigned int aTimeout_ms = 20000);


        /** Drop table.
         *
         * This function Drops the table currently set at TargetTable.
         *
         * \note This function may drop ANY table currently set to
         * TargetTable at which the current user has database access
         * right 'DROP' to.
         *
         * \note BEWARE: DESTROYS ALL STORED DATA!
         *
         * @param[in] aTimeout_ms Maximum time to wait for the operation to complete.
         * @return              'true' on success, 'false' on error.
         *
         */
        bool DropTable(const unsigned int aTimeout_ms = 20000);


        /** Insert new Object Row.
         *
         * This functions inserts a new entry in the
         * database. Currently there is no function for adding
         * multiple entries with one call, but such may be added if
         * the performance hit from single insert is seen too big.
         * 
         * @param[in] aNewRow   New row to insert.
         * @param[in] aTimeout_ms Maximum time to wait for the operation to complete.
         * @return              'true' on success, 'false' on error.
         *
         */
        bool InsertObjectDBRow(const SObject &aNewRow, 
                                       const unsigned int aTimeout_ms = 20000);


        /** Get Objects from database.
         *
         * This function fetches Objects from the database
         * using the given Where clause. Limits can also be given with
         * the offset and RowCount parameters. Number of returned rows
         * can be examined with standard 'size()' call on the
         * resultarray.
         *
         * @param[out] aResultArray Result array is stored here.
         * @param[in] aWhere    Where clause to use. 
         *                      The given clause is inserted as 'WHERE <clause>'
         * @param[in] aOffset   Limit offset (first entry of search to return)
         * @param[in] aRowCount Number of rows to return starting from aOffset
         * @param[in] aTimeout_ms Maximum time to wait for the operation to complete.
         * @return              'true' on success, 'false' on error.
         * 
         */
        bool GetObjects(TObjectResultTable &aResultArray,
                        const std::string &aWhere,
                        const unsigned int aOffset = 0,
                        const unsigned int aRowCount = 1000,
                        const unsigned int aTimeout_ms = 20000);



        /** Return List of objects.
         *
         *
         *
         * Required fields:
         * id, name
         */
        bool GetObjectNameList(TObjectNameAndIdResultTable &aResultArray,
                               const std::string &aWhere = "1 = 1",
                               const unsigned int aOffset = 0,
                               const unsigned int aRowCount = 1000,
                               const unsigned int aTimeout_ms = 20000);

        /** Return full object.
         *
         * Required fields:
         * all
         *
         */
        bool GetObject(SObject &aResult,
                       const unsigned int &aObjectId);
          

      private:
        CObjectDB(const CObjectDB &) 
          : CSQLExtension() {}
        CObjectDB &operator=(const CObjectDB &) { return *this; }
      };
      
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////

      bool CObjectDB::DropTable(const unsigned int aTimeout_ms)
      {
        bool result = false;
        std::stringstream querys;
        querys << "DROP TABLE `" + iTargetTable + "`" << std::endl;  
        
        // Convert to standard string
        const std::string query(querys.str());
        
        // Execute
        CSQLResult sqlresult;
        CSQLQuery sqlquery(query);
        result = iSQLClient->ExecuteQuery(sqlresult, sqlquery, aTimeout_ms);
        if (result) {
          // Check that result contains a table.
          if (sqlresult.GotAffectedRows()) {
            dPrint(ODDEBUG(1),"Affected rows %lld, all OK.", sqlresult.GetAffectedRows());
            result = true;
            
          } else if (sqlresult.GotError()) { 
            // Query Failed! :(
            iLastError = sqlresult.GetLastError();
            result = false;
            
          } else {
            iLastError = "Unknown error!";
            result = false;
            
          }
          
        } else {
          iLastError = "Query failed or timed out";
          dPrint(ODWARN,"Query failed or timed out :(");
          
        }
        return result;
      }

      bool CObjectDB::CreateTable(const unsigned int aTimeout_ms)
      {
        bool result = false;
        std::string database = "motti";
        std::string table = "mapObjects";
        std::string command;
        std::stringstream finals;

        // Create SQL clause
        std::string create_command = "CREATE TABLE `robot`.`world` ("
          "`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'unique key for objects, reserved 0 for world and 1 for robot',"
          "PRIMARY KEY(`id`)"
          ")"
          "ENGINE = MYISAM;"
          ""
          "ALTER TABLE `robot`.`world` MODIFY COLUMN `id` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT COMMENT 'unique key for objects, reserved 0 for world and 1 for robot',"
          " ADD COLUMN `name` CHAR(16) ASCII NOT NULL COMMENT 'Human readable and associative name for object' AFTER `id`,"
          " ADD COLUMN `node` INTEGER UNSIGNED NOT NULL DEFAULT 1 COMMENT 'id of parent node/object' AFTER `name`,"
          " ADD COLUMN `placeX` FLOAT  NOT NULL DEFAULT 0.0 COMMENT 'objects pose in nodes co-ordinate frame' AFTER `node`,"
          " ADD COLUMN `placeY` FLOAT  NOT NULL DEFAULT 0.0 COMMENT 'objects pose in nodes co-ordinate frame' AFTER `placeX`,"
          " ADD COLUMN `placeZ` FLOAT  NOT NULL DEFAULT 0.0 COMMENT 'objects pose in nodes co-ordinate frame' AFTER `placeY`,"
          " ADD COLUMN `placeA` FLOAT  NOT NULL DEFAULT 0.0 COMMENT 'objects pose in nodes co-ordinate frame, orientation euler angles in X,Y,Z order in node`s frame' AFTER `placeZ`,"
          " ADD COLUMN `placeB` FLOAT  NOT NULL DEFAULT 0.0 COMMENT 'objects pose in nodes co-ordinate frame, orientation euler angles in X,Y,Z order in node`s frame' AFTER `placeA`,"
          " ADD COLUMN `placeG` FLOAT  NOT NULL DEFAULT 0.0 COMMENT 'objects pose in nodes co-ordinate frame, orientation euler angles in X,Y,Z order in node`s frame' AFTER `placeB`,"
          " ADD COLUMN `placeX_var` FLOAT  NOT NULL DEFAULT 99.9 COMMENT 'objects pose in nodes co-ordinate frame' AFTER `placeX`,"
          " ADD COLUMN `placeY_var` FLOAT  NOT NULL DEFAULT 99.9 COMMENT 'objects pose in nodes co-ordinate frame' AFTER `placeY`,"
          " ADD COLUMN `placeZ_var` FLOAT  NOT NULL DEFAULT 99.9 COMMENT 'objects pose in nodes co-ordinate frame' AFTER `placeZ`,"
          " ADD COLUMN `placeA_var` FLOAT  NOT NULL DEFAULT 99.9 COMMENT 'objects pose in nodes co-ordinate frame' AFTER `placeA`,"
          " ADD COLUMN `placeB_var` FLOAT  NOT NULL DEFAULT 99.9 COMMENT 'objects pose in nodes co-ordinate frame' AFTER `placeB`,"
          " ADD COLUMN `placeG_var` FLOAT  NOT NULL DEFAULT 99.9 COMMENT 'objects pose in nodes co-ordinate frame' AFTER `placeG`,"
          " ADD COLUMN `shape` CHAR(16) ASCII NOT NULL DEFAULT 'ball' COMMENT 'basic shape of object used in visualization (line, plane, ball, cube, cone)' AFTER `placeG_var`,"
          " ADD COLUMN `size` FLOAT  NOT NULL DEFAULT 0.5 COMMENT '[m]' AFTER `shape`,"
          " ADD COLUMN `size_var` FLOAT  NOT NULL DEFAULT 99.9 COMMENT '[m]' AFTER `size`,"
          " ADD COLUMN `weight` FLOAT  NOT NULL DEFAULT 1.0 COMMENT '[kg]' AFTER `size_var`,"
          " ADD COLUMN `weight_var` FLOAT  NOT NULL DEFAULT 99.9 COMMENT '[kg]' AFTER `weight`,"
          " ADD COLUMN `color` FLOAT  NOT NULL DEFAULT 0.5 COMMENT 'normalized hue (0.0-1.0)' AFTER `weight_var`,"
          " ADD COLUMN `color_var` FLOAT  NOT NULL DEFAULT 99.9 COMMENT 'hue' AFTER `color`,"
          " ADD COLUMN `time` DOUBLE UNSIGNED NULL COMMENT '[s] last time evidence got from object' AFTER `color_var`,"
          " ADD COLUMN `pic_front` BLOB NOT NULL COMMENT 'sample image of object from front' AFTER `time`,"
          " ADD COLUMN `pic_back` BLOB NOT NULL COMMENT 'sample image of object from back' AFTER `pic_front`,"
          " ADD COLUMN `shape_ratio` FLOAT  NOT NULL DEFAULT 1.0 COMMENT 'ratio of two primary size measures of the shape' AFTER `size_var`;";
        
        // Convert to standard string
        const std::string query(create_command);

        // Execute
        CSQLResult sqlresult;
        CSQLQuery sqlquery(query);
        result = iSQLClient->ExecuteQuery(sqlresult, sqlquery, aTimeout_ms);
        if (result) {
          // Check that result contains a table.
          if (sqlresult.GotAffectedRows()) {
            dPrint(ODDEBUG(1),"Affected rows %lld, all OK.", sqlresult.GetAffectedRows());
            result = true;

          } else if (sqlresult.GotError()) { 
            // Query Failed! :(
            iLastError = sqlresult.GetLastError();
            result = false;

          } else {
            iLastError = "Unknown error!";
            result = false;

          }

        } else {
          iLastError = "Query failed or timed out";
          dPrint(ODWARN,"Query failed or timed out :(");
            
        }
        return result;
      }

      bool CObjectDB::InsertObjectDBRow(const SObject &aNewRow, const unsigned int aTimeout_ms)
      {
        /*
        bool result = true;
        std::stringstream querys;
        querys << "INSERT INTO `" << iTargetTable << "` (" << std::endl <<
          "`id` ," << std::endl <<
          "`group_id` ," << std::endl <<
          "`logical_id` ," << std::endl <<
          "`type_id` ," << std::endl <<
          "`x` ," << std::endl <<
          "`y`" << std::endl <<
          ")" << std::endl <<
          "VALUES (" << std::endl <<
          "'', '" << aNewRow.group_id << 
          "', '" << aNewRow.logical_id << 
          "', '" << aNewRow.type_id << 
          "', '" << aNewRow.x << 
          "', '" << aNewRow.y << 
          "'" << std::endl <<
          ");" << std::endl;
        
        std::string query(querys.str());
        //        dPrint(ODTEST,"Query:\n'%s'", query.c_str());
        
        CSQLResult sqlresult;
        CSQLQuery sqlquery(query);
        result = iSQLClient->ExecuteQuery(sqlresult, sqlquery, aTimeout_ms);
        if (result) {
          // Check that result contains a table.
          if (sqlresult.GotAffectedRows()) {
            dPrint(ODDEBUG(1),"Affected rows %lld, all OK.", sqlresult.GetAffectedRows());
            result = sqlresult.GetAffectedRows() == 1;

          } else if (sqlresult.GotError()) { 
            // Query Failed! :(
            iLastError = sqlresult.GetLastError();
            result = false;

          } else {
            iLastError = "Unknown error!";
            result = false;

          }

        } else {
          iLastError = "Query failed or timed out";
          dPrint(ODWARN,"Query failed or timed out :(");
            
          }
          return result;*/
        return false;
      }
      
      bool CObjectDB::GetObjects(TObjectResultTable &aResultArray,
                                                 const std::string &aWhere,
                                                 const unsigned int aOffset,
                                                 const unsigned int aRowCount,
                                                 const unsigned int aTimeout_ms) 
      {
        // Create and construct stream objects (even though a lot
        // slower than sprintf conversion, use this)
        std::stringstream offsetStr; offsetStr << aOffset;
        std::stringstream rowcountStr; rowcountStr << aRowCount;
        
        // Construct Query
        const std::string query("SELECT `id`,`group_id`,`logical_id`,`type_id`,`x`,`y` FROM " + 
                                iTargetTable + " " + 
                                "WHERE " + aWhere +
                                " LIMIT " + 
                                offsetStr.str() + 
                                "," + 
                                rowcountStr.str()); 
        
        // Execute Query.
        CSQLResult sqlresult;
        CSQLQuery sqlquery(query);
        bool result = iSQLClient->ExecuteQuery(sqlresult, sqlquery, aTimeout_ms);
        if (result) {
          aResultArray.clear();
          
          // Check that result contains a table.
          if (sqlresult.GotTable()) {
            
            // Get table reference for easier (faster) access.
            const CSQLTable &t = sqlresult.GetTable();
            
            // Get rowcount and iterate rows.
            const unsigned int rowcount = t.GetRowCount();
            
            // Loop through all rows, watch those results!
            for (unsigned int i = 0; 
                 i < rowcount && result; 
                 ++i) {
              aResultArray.push_back(SObject(t.GetRow(i)));
            }

            result = true;
          } else if (sqlresult.GotError()) { 
            // Query Failed! :(
            iLastError = sqlresult.GetLastError();
            result = false;

          } else {
            iLastError = "Unknown error!";
            result = false;

          }

        } else {
          iLastError = "Query failed or timed out";
          dPrint(ODWARN,"Query failed or timed out :(");
            
        }
        return result;
      }

      bool CObjectDB::GetObjectNameList(TObjectNameAndIdResultTable &aResultArray,
                                        const std::string &aWhere,
                                        const unsigned int aOffset,
                                        const unsigned int aRowCount,
                                        const unsigned int aTimeout_ms)
      {
        // Create and construct stream objects (even though a lot
        // slower than sprintf conversion, use this)
        std::stringstream offsetStr; offsetStr << aOffset;
        std::stringstream rowcountStr; rowcountStr << aRowCount;
        
        // Construct Query
        const std::string query("SELECT `id`,`name` FROM " + 
                                iTargetTable + " " + 
                                "WHERE " + aWhere +
                                " LIMIT " + offsetStr.str() + 
                                "," + rowcountStr.str()); 
        
        // Execute Query.
        CSQLResult sqlresult;
        CSQLQuery sqlquery(query);
        bool result = iSQLClient->ExecuteQuery(sqlresult, sqlquery, aTimeout_ms);
        if (result) {
          aResultArray.clear();
          
          // Check that result contains a table.
          if (sqlresult.GotTable()) {
            
            // Get table reference for easier (faster) access.
            const CSQLTable &t = sqlresult.GetTable();
            
            // Get rowcount and iterate rows.
            const unsigned int rowcount = t.GetRowCount();
            
            // Loop through all rows, watch those results!
            for (unsigned int i = 0; 
                 i < rowcount && result; 
                 ++i) {
              aResultArray.push_back(SObjectNameAndId(t.GetRow(i)));
            }

            result = true;
          } else if (sqlresult.GotError()) { 
            // Query Failed! :(
            iLastError = sqlresult.GetLastError();
            result = false;

          } else {
            iLastError = "Unknown error!";
            result = false;

          }

        } else {
          iLastError = "Query failed or timed out";
          dPrint(ODWARN,"Query failed or timed out :(");
            
        }
        return result; 
      }

      ///////////////////////////////////////////////////////////////////

      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
      /////////////////////////////////////////////////////////////////////////
    }
  }
}
#endif // _MACI_INTERFACE_SQL_EXTENSION_OBJECTDB_HPP_
